<?php

class mPartners extends CI_Model {
    
        ########################################################################
        ####### DATA PRODUCT ##############
        ########################################################################
        function getList()
        {    
                $dblokal  = $this->load->database("default", TRUE);
                $query = $dblokal->query("SELECT DISTINCT imap.item_code as mvalue, CONCAT_WS(' | ', imap.item_code, itm.item_name) as mdisplay
                                        FROM t_mtr_item_mapping imap	
                                        JOIN t_mtr_item itm on itm.item_code = imap.item_code
                                        ORDER BY mdisplay ASC");
                /*$query = $dblokal->query("SELECT DISTINCT imap.item_code as mvalue, itm.item_name as mdisplay
                                        FROM t_mtr_item_mapping imap	
                                        JOIN t_mtr_item itm on itm.item_code = imap.item_code
                                        ORDER BY mdisplay ASC");*/
                if( $query->num_rows() > 0 ) {
                        return $query->result();
                } else {
                        return array();
                }
	}
        
        function getProductById($id)
        {    
                $dblokal  = $this->load->database("default", TRUE);
                $query = $dblokal->query("SELECT t_mtr_item.*,t_mtr_item_group.item_group_name
                                         FROM t_mtr_item,t_mtr_item_group
                                         WHERE t_mtr_item.item_group_id = t_mtr_item_group.item_group_id
                                         AND item_code='$id' ");
                if( $query->num_rows() > 0 ) {
                        return $query->result();
                } else {
                        return array();
                }
	}
        
        function get($tgl="")
        {    
                $dblokal  = $this->load->database("default", TRUE);
                if($tgl == "") $tgl = date('Y-m-d');
                $query = $dblokal->query("SELECT t_mtr_item.*,t_mtr_item_group.item_group_name
                                         FROM t_mtr_item,t_mtr_item_group
                                         WHERE t_mtr_item.item_group_id = t_mtr_item_group.item_group_id
                                         AND shipment_date='". $tgl ."' AND reseller_price > 0");
                if( $query->num_rows() > 0 ) {
                        return $query->result();
                } else {
                        return array();
                }
	}
        
        function gets()
        {    
                $dblokal  = $this->load->database("default", TRUE);
                $query = $dblokal->query("SELECT t_mtr_item.*,t_mtr_item_group.item_group_name, v_combo_sales_affected.member_display as sales
                                         FROM t_mtr_item,t_mtr_item_group,v_combo_sales_affected
                                         WHERE t_mtr_item.item_group_id = t_mtr_item_group.item_group_id and v_combo_sales_affected.member_value = 
                                         AND reseller_price >= 0");
                if( $query->num_rows() > 0 ) {
                        return $query->result();
                } else {
                        return array();
                }
	}
        
        function getTerritoryName()
        {    
                $dblokal  = $this->load->database("default", TRUE);
                $userlogin = $this->session->userdata('username');
                $query = $dblokal->query("select territory_name from t_mtr_territory where territory_id = f_get_cluster('$userlogin');");
                if( $query->num_rows() > 0 ) {
                        foreach($query->result() as $r){
                                return $r->territory_name;
                        }
                } else {
                        return "";
                }
	}
        
        function updateProduct(){
                #$dblokal  = $this->load->database("default", TRUE);
                #$dblokal->query("UPDATE t_mtr_item SET reseller_price='". $harga ."' WHERE item_code='". $id ."' AND shipment_date='". date("Y-m-d") ."'");
                
                $dblokal  = $this->load->database("default", TRUE);
                
                $territory_id           = "";
                $item_code              = $this->input->post('item_code');
                $pricing_date           = $this->input->post('pricing_date');
                $retail_price           = $this->input->post('retail_price');
                $condition_type         = $this->input->post('conditional_type');
                $condition_value        = $this->input->post('conditional_value');
                $sales_affected         = $this->input->post('sales_affected');
                
                $data = array("territory_id"=>$territory_id,
                              "item_code"=>$item_code,
                              "pricing_date"=>$pricing_date,
                              "retail_price"=>$retail_price,
                              "condition_type"=>$condition_type,
                              "condition_value"=>$condition_value,
                              "sales_affected"=>$sales_affected,
                              "created_by"=>$this->session->userdata('username'));
                
                $dblokal->insert("t_trx_pricing",$data);
        }
        
        function savePricing(){
                $dblokal  = $this->load->database("default", TRUE);
                
                $item_code      = $this->input->post("item_code");
				$pricing_date   = $this->input->post("pricing_date");
				$retail_price   = $this->input->post("retail_price");
				$condition_type = $this->input->post("condition_type");
				$condition_value= $this->input->post("condition_value");
				$sales_affected = $this->input->post("sales_affected");
                $selling_type= $this->input->post("selling_type");
				
				$terid = $this->mglobal->showdata("territory_id","t_mtr_territory",array('user_id'=>$this->session->userdata('username')),"dblokal");
                $data = array("territory_id"=>$terid,
                              "item_code"=>$item_code,
                              "pricing_date"=>$pricing_date,
                              "retail_price"=>$retail_price,
                              "condition_type"=>$condition_type,
                              "condition_value"=>$condition_value,
                              "sales_affected"=>$sales_affected,
							  "selling_type"=>$selling_type,
                              "created_by"=>$this->session->userdata('username'));
                
                $dblokal->insert("t_trx_pricing",$data);
        }
        
        function getGroup($itemCode){
                $dblokal  = $this->load->database("default", TRUE);
		$query = $dblokal->query("select f_get_item_group('$itemCode') as group_name");
                foreach($query->result() as $r){
                        return $r->group_name;
                }
	}
        
        function getPrice($itemCode){
                $dblokal  = $this->load->database("default", TRUE);
		$query = $dblokal->query("select f_get_price_retail('$itemCode') as price");
                foreach($query->result() as $r){
                        return $r->price;
                }
	}
        
        function getSalesAffected(){
                $dblokal  = $this->load->database("default", TRUE);
				$query = $dblokal->query("select * from v_combo_sales_affected;");
                return $query->result();
		}
        
        function getConditionType(){
                $dblokal  = $this->load->database("default", TRUE);
				$query = $dblokal->query("select * from v_combo_condition_type;");
                return $query->result();
		}
		
		function getSellingType(){
                $dblokal  = $this->load->database("default", TRUE);
				$query = $dblokal->query("select * from v_combo_agent_selling_type;");
                return $query->result();
		}
        
        function getPricesData(){
                $dblokal  = $this->load->database("default", TRUE);
				$query = $dblokal->query("select  imap.item_name, imap.item_code, price.pricing_date, 
                                            price.retail_price,price.condition_type,price.condition_value,
                                            price.selling_type, grp.user_group_name from t_trx_pricing price inner join 
                                            t_mtr_item_mapping imap on price.item_code=imap.item_code inner join t_mtr_user_group grp 
                                            on grp.user_group_id=price.sales_affected  
                                            where price.territory_id=f_get_cluster('".$this->session->userdata('username')."')");
                return $query->result();
		}
        
       	function getProductsData(){
       	    
                $dblokal  = $this->load->database("default", TRUE);
				$query = $dblokal->query("select igrp.item_group_name, 
                                            itm.iccid,itm.mdn,imap.item_name, imap.item_code,itm.original_distributor_code,
                                            itm.original_ship_to_code,itm.shipment_date,itm.istatus from t_mtr_item itm 
                                            inner join t_mtr_item_mapping imap on itm.item_mapping_id=imap.item_mapping_id 
                                            inner join t_mtr_item_group igrp on igrp.item_group_id=imap.item_group_id  
                                            inner join t_mtr_distributor dist on dist.distributor_id=itm.distributor_id
                                            where dist.territory_id=f_get_cluster('".$this->session->userdata('username')."')");
                return $query->result();
		}
        
        function searchProduct(){
                $dblokal  = $this->load->database("default", TRUE);
				$query = $dblokal->query("select itm.iccid,itm.mdn,igrp.item_group_name, imap.item_name, dist.distributor_name,
                                            split_part(f_get_territory_path(dist.territory_id) , '>', 1) as region,
                                            split_part(f_get_territory_path(dist.territory_id) , '>',2) as cluster from t_mtr_item itm 
                                            inner join t_mtr_distributor dist on itm.distributor_id=dist.distributor_id inner join 
                                            t_mtr_item_mapping imap on imap.item_code=itm.item_code inner join 
                                            t_mtr_item_group igrp on imap.item_group_id=igrp.item_group_id
                                            where itm.iccid='".$this->input->post('param')."' or itm.mdn='".$this->input->post('param')."'");
                return $query->result_array();
        }
        
        function getSales($user_group_id){
            if ($user_group_id==9){
                $partner_type=6;
            } elseif ($user_group_id==8){
                $partner_type=5;
            }
                $dblokal=$this->load->database("default", TRUE);
                $query=$dblokal->Query("
                                            select distinct usr.user_id, usr.user_name||' | '||ustat.status_name as user_name 
                                                from t_mtr_user usr 
                                                    inner join t_mtr_user_status ustat
                                                        on usr.istatus=ustat.status_id 
                                                    left join t_mtr_territory ter
                                                        on ter.user_id=usr.user_id
                                            where usr.user_group_id='$user_group_id' and f_get_cluster(usr.reporting_to)=f_get_cluster('".$this->session->userdata('username')."')
                                            union
                                            select distinct usr.user_id, usr.user_name||' | Migrate' as user_name 
                                                from t_mtr_territory ter
                                                    inner join t_mtr_user usr
                                                        on ter.user_id=usr.user_id
                                            where usr.user_group_id<>'$user_group_id' and f_get_cluster(usr.reporting_to)=f_get_cluster('".$this->session->userdata('username')."')
                                                and ter.territory_type_id='$partner_type'
                                            union
                                            select distinct usr.user_id, usr.user_name||' | Migrate' as user_name 
                                                from t_mtr_territory ter
                                                    inner join t_mtr_user usr
                                                        on ter.user_id=usr.user_id
                                            where                                            
                                            	usr.user_group_id in (select user_group_id from t_mtr_user_group where is_use_handset=1) and
                                            			f_get_territory_id(usr.reporting_to) <> f_get_territory_id('".$this->session->userdata('username')."')
                                            	and ter.territory_id=f_get_territory_id('".$this->session->userdata('username')."')
                                                                                        
                                        ");
                return $query->result();
        }
        
        
        function getPartners($user_group_id,$user_id=""){
                $dblokal=$this->load->database("default", TRUE);
                if($user_group_id=='8'){
                    if ($user_id=="Unmaintained")
                                $query=$dblokal->Query("select ter.territory_id,otl.outlet_id as partner_id, otl.outlet_name as partner_name,otl.address as partner_address,otl.city as partner_city from t_mtr_territory ter  inner join t_mtr_outlet otl on otl.territory_id=ter.territory_id where ter.user_id is NULL and  otl.istatus=1 and ter.parent_id=(select territory_id from t_mtr_territory where user_id=('".$this->session->userdata('username')."'))");
                    else        
                        $query=$dblokal->Query("select ter.territory_id,otl.outlet_id as partner_id, otl.outlet_name as partner_name,otl.address as partner_address,otl.city as partner_city from t_mtr_territory ter  inner join t_mtr_outlet otl on otl.territory_id=ter.territory_id where ter.user_id='$user_id' and otl.istatus=1 ");
               } else{
                    if ($user_id=="Unmaintained")
                        $query=$dblokal->Query("select ter.territory_id, agt.agent_id as partner_id, agt.agent_name as partner_name,agt.agent_address as partner_address,agt.agent_city as partner_city from t_mtr_territory ter  inner join t_mtr_agent agt on agt.territory_id=ter.territory_id  where ter.user_id is NULL and  agt.istatus=1 and ter.parent_id=(select territory_id from t_mtr_territory where user_id=('".$this->session->userdata('username')."'))");
                    else                
                        $query=$dblokal->Query("select ter.territory_id,  agt.agent_id as partner_id, agt.agent_name as partner_name,agt.agent_address as partner_address,agt.agent_city as partner_city from t_mtr_territory ter  inner join t_mtr_agent agt on agt.territory_id=ter.territory_id  where ter.user_id='$user_id' and  agt.istatus=1");
                }
                return $query->result();
        }
        
         function getRegion(){
                $dblokal=$this->load->database("default", TRUE);
                $query=$dblokal->Query("select territory_id,territory_name from t_mtr_territory where territory_type_id=2 and istatus=1 order by territory_name");
                return $query->result();
        }
        
         function getCluster($region_id){
                $dblokal=$this->load->database("default", TRUE);
                $query=$dblokal->Query("select user_id,territory_name from t_mtr_territory where territory_type_id=3 and istatus=1 and parent_id='$region_id' order by territory_name");
                return $query->result();
        }
        
        function getSalesFromCluster($reporting_to,$user_group_id){
                $dblokal=$this->load->database("default", TRUE);
                $query=$dblokal->Query("select user_id,user_name from t_mtr_user where user_group_id='$user_group_id' and f_get_cluster(reporting_to)=f_get_cluster('$reporting_to') and istatus=1");
                return $query->result();
        }
        
        function getPartnersFromCluster($reporting_to,$user_group_id,$user_id=""){
                $dblokal=$this->load->database("default", TRUE);
                if($user_group_id=='8'){
                    if ($user_id=="Unmaintained")
                                $query=$dblokal->Query("select ter.territory_id,otl.outlet_id as partner_id, otl.outlet_name as partner_name,otl.address as partner_address,otl.city as partner_city from t_mtr_territory ter  inner join t_mtr_outlet otl on otl.territory_id=ter.territory_id where ter.user_id is NULL and  otl.istatus=1 and ter.parent_id=(select territory_id from t_mtr_territory where user_id=('".$reporting_to."'))");
                    else        
                        $query=$dblokal->Query("select ter.territory_id,otl.outlet_id as partner_id, otl.outlet_name as partner_name,otl.address as partner_address,otl.city as partner_city from t_mtr_territory ter  inner join t_mtr_outlet otl on otl.territory_id=ter.territory_id where ter.user_id='$user_id' and otl.istatus=1 ");
               } else{
                    if ($user_id=="Unmaintained")
                        $query=$dblokal->Query("select ter.territory_id, agt.agent_id as partner_id, agt.agent_name as partner_name,agt.agent_address as partner_address,agt.agent_city as partner_city from t_mtr_territory ter  inner join t_mtr_agent agt on agt.territory_id=ter.territory_id  where ter.user_id is NULL and  agt.istatus=1 and ter.parent_id=(select territory_id from t_mtr_territory where user_id=('".$reporting_to."'))");
                    else                
                        $query=$dblokal->Query("select ter.territory_id,  agt.agent_id as partner_id, agt.agent_name as partner_name,agt.agent_address as partner_address,agt.agent_city as partner_city from t_mtr_territory ter  inner join t_mtr_agent agt on agt.territory_id=ter.territory_id  where ter.user_id='$user_id' and  agt.istatus=1");
                }
                return $query->result();
        }
        
        function getParentTerritory($user_id){
            $dblokal=$this->load->database("default", TRUE);
                $query=$dblokal->Query("select ter.territory_id from t_mtr_territory ter inner join t_mtr_user usr on usr.reporting_to=ter.user_id  where usr.user_id='$user_id' ");
                return $query->row()->territory_id;
        }
        
        function migratePartners($dataMigration){
          // print_r($dataMigration);
            $this->db->update('t_mtr_territory',$dataMigration,array('territory_id'=>$dataMigration['territory_id']));
          // echo 'a';
        }
        
} //end class